/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BookType;

import BookCategory.BookCategoryBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(BookTypeBean btb) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into book_type values(BOOKTYPEID.nextval,?,?,sysdate)");
            pre.setInt(1, btb.getBookCategoryId());
            pre.setString(2, btb.getBookType());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return k;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(book_type_id) from book_type");
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                i = rs.getInt(1);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static Vector viewSpecific(int cid) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from BOOK_TYPE left join BOOK_CATEGORY on BOOK_TYPE .BOOK_CATEGORY_id = BOOK_CATEGORY.BOOK_CATEGORY_id where book_type_id=?");
            pre.setInt(1, cid);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                BookTypeBean btb = new BookTypeBean();
                BookCategoryBean bcb = new BookCategoryBean();
                btb.setBookTypeId(rs.getInt(1));
                btb.setBookCategoryId(rs.getInt(2));
                btb.setBookType(rs.getString(3));
                btb.setEntryDate(rs.getDate(4));
                bcb.setBookCategoryId(rs.getInt(5));
                bcb.setBookCategory(rs.getString(6));
                bcb.setEntryDate(rs.getDate(7));
                btb.setBookCategoryBean(bcb);
                list.add(btb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector getBookType(int cid) throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from BOOK_TYPE where book_category_id=?");
            pre.setInt(1, cid);
            ResultSet rs = pre.executeQuery();
            while(rs.next()) {
                BookTypeBean btb = new BookTypeBean();
                btb.setBookTypeId(rs.getInt(1));
                btb.setBookCategoryId(rs.getInt(2));
                btb.setBookType(rs.getString(3));
                btb.setEntryDate(rs.getDate(4));
                list.add(btb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from BOOK_TYPE left join BOOK_CATEGORY on BOOK_TYPE .BOOK_CATEGORY_id = BOOK_CATEGORY.BOOK_CATEGORY_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                BookTypeBean btb = new BookTypeBean();
                BookCategoryBean bcb = new BookCategoryBean();
                btb.setBookTypeId(rs.getInt(1));
                btb.setBookCategoryId(rs.getInt(2));
                btb.setBookType(rs.getString(3));
                btb.setEntryDate(rs.getDate(4));
                bcb.setBookCategoryId(rs.getInt(5));
                bcb.setBookCategory(rs.getString(6));
                bcb.setEntryDate(rs.getDate(7));
                btb.setBookCategoryBean(bcb);
                list.add(btb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }

    public static int edit(BookTypeBean btb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update book_type set book_category_id=?, book_type=?, entry_date=sysdate where book_type_id=?");
            pre.setInt(1, btb.getBookCategoryId());
            pre.setString(2, btb.getBookType());
            pre.setInt(3, btb.getBookTypeId());
            i = pre.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int delete(BookTypeBean btb) throws SQLException {
        
            int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from book_type where book_type_id=?");
            pre.setInt(1, btb.getBookTypeId());
            i = pre.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }return i;
    }
}
